package com.bw.dao;

import com.bw.bean.Sugar;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SugarDao {
    @Select("select c2.`name`,sum(o.final_total_amount) `value` from order_info o \n" +
            "LEFT JOIN order_detail d ON o.id=d.order_id\n" +
            "LEFT JOIN sku_info s ON s.id=d.sku_id\n" +
            "LEFT JOIN base_category3 c3 ON s.category3_id=c3.id\n" +
            "LEFT JOIN base_category2 c2 ON c3.category2_id=c2.id \n" +
            "where c2.name is not null\n" +
            "GROUP BY c2.`name`")
    List<Sugar> getList1();

    @Select("select yys `name`,sum(final_total_amount) `value` from order_info where yys is not null GROUP BY yys\n")
    List<Sugar> getList3();

    @Select("select t.`name`,\n" +
            "if(u.gender='F','女','男') gender,\n" +
            "sum(o.final_total_amount) `value`\n" +
            "from order_info o \n" +
            "LEFT JOIN user_info u ON o.user_id=u.id\n" +
            "LEFT JOIN order_detail d ON o.id=d.order_id\n" +
            "LEFT JOIN sku_info s ON s.id=d.sku_id\n" +
            "LEFT JOIN base_trademark t ON t.id=s.tm_id\n" +
            "where t.`name` is not null \n" +
            "GROUP BY t.`name`,gender")
    List<Sugar> getList4();
}
